<!DOCTYPE html>
<html lang="en">
  <head>
    <title>
        MySQL-3-SQL-优化 - rulerLwx Blog
      </title>
        <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="viewport"
      content="width=device-width, initial-scale=1, maximum-scale=1, minimum-scale=1, user-scalable=no, minimal-ui">
    <meta name="renderer" content="webkit">
    <meta http-equiv="Cache-Control" content="no-transform" />
    <meta http-equiv="Cache-Control" content="no-siteapp" />
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black">
    <meta name="format-detection" content="telephone=no,email=no,adress=no">
    
    <meta name="theme-color" content="#000000" />
    
    <meta http-equiv="window-target" content="_top" />
    
    
    <meta name="description" content="概念 逻辑分层 存储引擎 InnoDB：事务优先（适合高并发操作，行锁） MyISAM：性能优先（表锁） sql优化原因：sql语句欠佳、索引失效、服" />
    <meta name="generator" content="Hugo 0.73.0 with theme pure" />
    <title>MySQL-3-SQL-优化 - rulerLwx Blog</title>
    
    
    <link rel="stylesheet" href="https://rulerLwx.gitee.io/css/style.min.c4bc7071f132c964c2116bca53b392933f377e5ca7b7051ed245187c621a2d3e.css">
    
    <link rel="stylesheet" href="https://cdn.staticfile.org/highlight.js/9.15.10/styles/github.min.css" async>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/tocbot/4.4.2/tocbot.css" async>
    <meta property="og:title" content="MySQL-3-SQL-优化" />
<meta property="og:description" content="概念 逻辑分层 存储引擎 InnoDB：事务优先（适合高并发操作，行锁） MyISAM：性能优先（表锁） sql优化原因：sql语句欠佳、索引失效、服" />
<meta property="og:type" content="article" />
<meta property="og:url" content="https://rulerLwx.gitee.io/2018/06/mysql-3-sql%E4%BC%98%E5%8C%96/" />
<meta property="article:published_time" content="2018-06-01T10:02:47+08:00" />
<meta property="article:modified_time" content="2018-06-01T10:02:47+08:00" />
<meta itemprop="name" content="MySQL-3-SQL-优化">
<meta itemprop="description" content="概念 逻辑分层 存储引擎 InnoDB：事务优先（适合高并发操作，行锁） MyISAM：性能优先（表锁） sql优化原因：sql语句欠佳、索引失效、服">
<meta itemprop="datePublished" content="2018-06-01T10:02:47&#43;08:00" />
<meta itemprop="dateModified" content="2018-06-01T10:02:47&#43;08:00" />
<meta itemprop="wordCount" content="5093">



<meta itemprop="keywords" content="" /><meta name="twitter:card" content="summary"/>
<meta name="twitter:title" content="MySQL-3-SQL-优化"/>
<meta name="twitter:description" content="概念 逻辑分层 存储引擎 InnoDB：事务优先（适合高并发操作，行锁） MyISAM：性能优先（表锁） sql优化原因：sql语句欠佳、索引失效、服"/>

    <!--[if lte IE 9]>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/classlist/1.1.20170427/classList.min.js"></script>
      <![endif]-->

    <!--[if lt IE 9]>
        <script src="https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js"></script>
      <![endif]-->
  </head>

  
  

  <body class="main-center theme-black" itemscope itemtype="http://schema.org/WebPage"><header class="header" itemscope itemtype="http://schema.org/WPHeader">
    <div class="slimContent">
      <div class="navbar-header">
        <div class="profile-block text-center">
          <a id="avatar" href="https://gitee.com/rulerLwx" target="_blank">
            <img class="img-circle img-rotate" src="https://rulerLwx.gitee.io/avatar.png" width="200" height="200">
          </a>
          <h2 id="name" class="hidden-xs hidden-sm">rulerLwx</h2>
          <h3 id="title" class="hidden-xs hidden-sm hidden-md">thinking...</h3>
          <small id="location" class="text-muted hidden-xs hidden-sm"><i class="icon icon-map-marker"></i>Guangzhou, China</small>
        </div><div class="search" id="search-form-wrap">
    <form class="search-form sidebar-form">
        <div class="input-group">
            <input type="text" class="search-form-input form-control" placeholder="Search" />
            <span class="input-group-btn">
                <button type="submit" class="search-form-submit btn btn-flat" onclick="return false;"><i
                        class="icon icon-search"></i></button>
            </span>
        </div>
        <div class="ins-search">
            <div class="ins-search-mask"></div>
            <div class="ins-search-container">
                <div class="ins-input-wrapper">
                    <input type="text" class="ins-search-input" placeholder="Type something..."
                        x-webkit-speech />
                    <button type="button" class="close ins-close ins-selectable" data-dismiss="modal"
                        aria-label="Close"><span aria-hidden="true">×</span></button>
                </div>
                <div class="ins-section-wrapper">
                    <div class="ins-section-container"></div>
                </div>
            </div>
        </div>
    </form>
</div>
        <button class="navbar-toggle collapsed" type="button" data-toggle="collapse" data-target="#main-navbar" aria-controls="main-navbar" aria-expanded="false">
          <span class="sr-only">Toggle navigation</span>
          <span class="icon-bar"></span>
          <span class="icon-bar"></span>
          <span class="icon-bar"></span>
        </button>
      </div>
      <nav id="main-navbar" class="collapse navbar-collapse" itemscope itemtype="http://schema.org/SiteNavigationElement" role="navigation">
        <ul class="nav navbar-nav main-nav">
            <li class="menu-item menu-item-home">
                <a href="/">
                    <i class="icon icon-home-fill"></i>
                  <span class="menu-title">Home</span>
                </a>
            </li>
            <li class="menu-item menu-item-archives">
                <a href="/posts/">
                    <i class="icon icon-archives-fill"></i>
                  <span class="menu-title">Archives</span>
                </a>
            </li>
            <li class="menu-item menu-item-categories">
                <a href="/categories/">
                    <i class="icon icon-folder"></i>
                  <span class="menu-title">Categories</span>
                </a>
            </li>
            <li class="menu-item menu-item-tags">
                <a href="/tags/">
                    <i class="icon icon-tags"></i>
                  <span class="menu-title">Tags</span>
                </a>
            </li>
            <li class="menu-item menu-item-about">
                <a href="/about/">
                    <i class="icon icon-cup-fill"></i>
                  <span class="menu-title">About</span>
                </a>
            </li>
        </ul>
      </nav>
    </div>
  </header>

<aside class="sidebar" itemscope itemtype="http://schema.org/WPSideBar">
  <div class="slimContent">
    
      <div class="widget">
    <h3 class="widget-title">Board</h3>
    <div class="widget-body">
        <div id="board">
            <div class="content">enjoy~
            </div>
        </div>
    </div>
</div>

      <div class="widget">
    <h3 class="widget-title"> Categories</h3>
    <div class="widget-body">
        <ul class="category-list">
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-framework/" class="category-list-link">java-framework</a><span class="category-list-count">38</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-front-end/" class="category-list-link">java-front-end</a><span class="category-list-count">11</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-se/" class="category-list-link">java-se</a><span class="category-list-count">21</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/java-senior/" class="category-list-link">java-senior</a><span class="category-list-count">4</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/linux/" class="category-list-link">linux</a><span class="category-list-count">13</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/tools/" class="category-list-link">tools</a><span class="category-list-count">1</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/%E6%8A%80%E6%9C%AF%E6%9D%82%E7%83%A9/" class="category-list-link">技术杂烩</a><span class="category-list-count">4</span></li>
            <li class="category-list-item"><a href="https://rulerLwx.gitee.io/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/" class="category-list-link">数据库</a><span class="category-list-count">15</span></li>
        </ul>
    </div>
</div>
      <div class="widget">
    <h3 class="widget-title"> Tags</h3>
    <div class="widget-body">
        <ul class="tag-list">
            
            
            <li class="tag-list-item"><a href="https://rulerLwx.gitee.io/tags/jvm/" class="tag-list-link">jvm</a><span
                    class="tag-list-count">1</span></li>
            
            
            <li class="tag-list-item"><a href="https://rulerLwx.gitee.io/tags/%E5%A4%9A%E7%BA%BF%E7%A8%8B/" class="tag-list-link">多线程</a><span
                    class="tag-list-count">2</span></li>
            
            
            <li class="tag-list-item"><a href="https://rulerLwx.gitee.io/tags/%E7%BD%91%E7%BB%9C%E7%BC%96%E7%A8%8B/" class="tag-list-link">网络编程</a><span
                    class="tag-list-count">3</span></li>
            
        </ul>

    </div>
</div>
      
<div class="widget">
    <h3 class="widget-title">Recent Posts</h3>
    <div class="widget-body">
        <ul class="recent-post-list list-unstyled no-thumbnail">
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E8%87%AA%E5%B7%B1%E5%8A%A8%E6%89%8B%E4%B8%80%E6%89%B9%E9%87%8F%E7%A7%BB%E5%8A%A8%E9%87%8D%E5%91%BD%E5%90%8D%E6%96%87%E4%BB%B6/" class="title">自己动手（一）——批量移动、重命名文件</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-27 17:50:02 &#43;0800 CST" itemprop="datePublished">2020-07-27</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E5%85%B3%E4%BA%8Einteger%E7%9A%84-128~127%E7%BC%93%E5%AD%98/" class="title">关于Integer的 -128~127缓存</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-11 16:56:21 &#43;0800 CST" itemprop="datePublished">2020-07-11</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E8%B7%A8%E5%9F%9F%E9%97%AE%E9%A2%98/" class="title">跨域问题</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-08 22:41:12 &#43;0800 CST" itemprop="datePublished">2020-07-08</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/%E4%B8%AA%E4%BA%BA%E5%8D%9A%E5%AE%A2%E6%90%AD%E5%BB%BA/" class="title">个人博客搭建</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-05 18:58:47 &#43;0800 CST" itemprop="datePublished">2020-07-05</time>
                    </p>
                </div>
            </li>
            <li>
                <div class="item-inner">
                    <p class="item-title">
                        <a href="https://rulerLwx.gitee.io/2020/07/mongodb-1/" class="title">MonggoDB-1</a>
                    </p>
                    <p class="item-date">
                        <time datetime="2020-07-03 18:58:47 &#43;0800 CST" itemprop="datePublished">2020-07-03</time>
                    </p>
                </div>
            </li>
        </ul>
    </div>
</div>
  </div>
</aside>

    
    
<aside class="sidebar sidebar-toc collapse" id="collapseToc" itemscope itemtype="http://schema.org/WPSideBar">
  <div class="slimContent">
    <h4 class="toc-title">Catalogue</h4>
    <nav id="toc" class="js-toc toc">

    </nav>
  </div>
</aside>
<main class="main" role="main"><div class="content">
  <article id="-" class="article article-type-" itemscope
    itemtype="http://schema.org/BlogPosting">
    
    <div class="article-header">
      <h1 itemprop="name">
  <a
    class="article-title"
    href="/2018/06/mysql-3-sql%E4%BC%98%E5%8C%96/"
    >MySQL-3-SQL-优化</a
  >
</h1>

      <div class="article-meta">
        
<span class="article-date">
  <i class="icon icon-calendar-check"></i>&nbsp;
<a href="https://rulerLwx.gitee.io/2018/06/mysql-3-sql%E4%BC%98%E5%8C%96/" class="article-date">
  <time datetime="2018-06-01 10:02:47 &#43;0800 CST" itemprop="datePublished">2018-06-01</time>
</a>
</span>
<span class="article-category">
  <i class="icon icon-folder"></i>&nbsp;
  <a class="article-category-link" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/"> 数据库 </a>
</span>

        <span class="post-comment"><i class="icon icon-comment"></i>&nbsp;<a href="/2018/06/mysql-3-sql%E4%BC%98%E5%8C%96/#comments"
            class="article-comment-link">Comments</a></span>
		<span class="post-wordcount hidden-xs" itemprop="wordCount">Word Count: 5093words</span>
		<span class="post-readcount hidden-xs" itemprop="timeRequired">Read Count: 11minutes </span>
      </div>
    </div>
    <div class="article-entry marked-body js-toc-content" itemprop="articleBody">
      <h1 id="概念">概念</h1>
<h2 id="逻辑分层">逻辑分层</h2>
<p><img src="https://gitee.com/leafsing/pic/raw/master/img/20200709082955.png" alt=""></p>
<h2 id="存储引擎">存储引擎</h2>
<ul>
<li>InnoDB：事务优先（适合高并发操作，行锁）</li>
<li>MyISAM：性能优先（表锁）</li>
</ul>
<p>sql优化原因：sql语句欠佳、索引失效、服务器参数设置不合理（缓存、线程数）</p>
<h2 id="sql解析过程">sql解析过程</h2>
<p>1）编写顺序</p>
<pre><code>select ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
</code></pre>
<p>2）解析顺序</p>
<pre><code>from ... on ... join ... where ... group by ... havint ... select ... order by ... limit ...
</code></pre>
<p>索引的数据结构</p>
<p>树：B+树（默认）、Hash树</p>
<h1 id="存储引擎-1">存储引擎</h1>
<p>存储引擎 = 算法 + IO</p>
<h2 id="isam">ISAM</h2>
<p>读非常快，但不支持事务</p>
<h2 id="myisam">MyISAM</h2>
<p>特点：</p>
<ul>
<li>MyISAM是ISAM扩展格式，不支持事务</li>
<li>数据越多，写操作效率越低，因为要维护数据表索引</li>
</ul>
<p>使用此引擎，生成三个文件：</p>
<ul>
<li>.frm：表结构信息</li>
<li>.MYD：数据文件</li>
<li>.MYI：表的索引信息</li>
</ul>
<h2 id="innodb">InnoDB</h2>
<p>InnoDB 存储引擎，5.7版本后，生成两个文件：</p>
<ul>
<li>.frm：表结构信息</li>
<li>.idb：表的索引信息</li>
</ul>
<p>特点：</p>
<ul>
<li>支持事务</li>
<li>数据多版本读取</li>
<li>锁定机制的改进</li>
<li>实现外键</li>
</ul>
<h3 id="innodb与myisam的区别">InnoDB与MyISAM的区别</h3>
<p>1）InnoDB支持事务，MyISAM不支持事务</p>
<p>对于 InnoDB ，每一条sql都默认封装成事务，自动提交，但这位会影响速度，所以最好将多条SQL语言放在 begin transaction 和 commit 之间，组成一个事务</p>
<p>2）InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB转换成 MyISAM 会失败</p>
<p>3）InnoDB 是聚集引擎，而  MyISAM 是非聚集引擎</p>
<p>InnoDB 是聚集引擎，数据文件和索引绑定在一起，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，再通过主键查询到数据。因此，主键不应该过大，主键太大，其它索引也会很大。</p>
<p>MyISAM 是非聚集引擎，数据文件是分离的，索引文件保存的是数据文件的指针，主键索引和辅助索引是独立的。</p>
<blockquote>
<p>注：聚集索引并不是在物理存储上是连续的，而是在逻辑上连续</p>
</blockquote>
<p>4）InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描，而 MyISAM 会用一个变量保存了整个表的行数。</p>
<p>5）InnoDB 不支持全文检索，而 MyISAM 支持，查询效率上 MyISAM 更高。</p>
<p>总结：MySQL 可以实现同一个数据库中，不同的表应用不同的存储引擎；主从数据库里同一张表，数据引擎也可以不一样；</p>
<p>参考：https://zhidao.baidu.com/question/396976850982201885.html</p>
<h2 id="memory">Memory</h2>
<p>将数据存储在内存中的引擎</p>
<h2 id="引擎常用命令">引擎常用命令</h2>
<pre><code class="language-sql">show engines;#查看支持的引擎
show variables like '%storage_engine%';#查看当前使用的存储引擎
show create table table_name;#查看table_name表使用的引擎
create table table_name(column_name,column_type) engine = engine_name#创建表时指定引擎
alter table table_name engine = engine_name;#修改表的引擎
</code></pre>
<p>如何修改默认的引擎？</p>
<p>在MySQL配置文件中修改<code>default-storage-engine=INNODB</code></p>
<ul>
<li>windows在：MySQL安装目录/my.ini</li>
<li>linux在：/etc/my.cnf</li>
</ul>
<h1 id="索引">索引</h1>
<h2 id="什么字段适合创建索引">什么字段适合创建索引</h2>
<ul>
<li>在经常需要搜索的列上</li>
<li>在作为主键的列上</li>
<li>在经常用在连接的列上，这些列主要是一些外键</li>
<li>在经常需要根据范围搜索的列上，此范围是连续的</li>
<li>在经常需要排序的列上</li>
<li>在经常用在where子句的列上</li>
</ul>
<h2 id="mysql索引种类">MySQL索引种类</h2>
<h3 id="b-tree索引">B-Tree索引</h3>
<p>B-Tree，balance tree</p>
<p>B-Tree中，每个结点包含：</p>
<ol>
<li>本结点所含 关键字 的个数</li>
<li>指向父结点的指针</li>
<li>关键字</li>
<li>指向子结点的指针</li>
</ol>
<p>对于一棵m 阶B-tree，每个结点至多可以拥有m 个子结点。各结点的关键字和可以拥
有的子结点数都有限制，规定m 阶B-tree 中，根结点至少有2 个子结点，除非根结点为叶
子节点，相应的，根结点中关键字的个数为1~m-1；非根结点至少有[m/2]（[]，向上取整）
个子结点，相应的，关键字个数为[m/2]-1~m-1。</p>
<p>B-tree 有以下特性：</p>
<ol>
<li>关键字集合分布在整棵树中；</li>
<li>任何一个关键字出现且只出现在一个结点中；</li>
<li>搜索有可能在非叶子结点结束；</li>
<li>其搜索性能等价于在关键字全集内做一次二分查找；</li>
<li>自动层次控制；</li>
</ol>
<h3 id="full-tree索引">Full-Tree索引</h3>
<p>Full-text 索引就是我们常说的全文索引，他的存储结构也是b-tree。主要是为了解决在
我们需要用like 查询的低效问题。只能解决’xxx%’的like 查询。如：字段数据为ABCDE，索
引建立为 A、AB、ABC、ABCD、ABCDE 五个。</p>
<h2 id="索引管理">索引管理</h2>
<p>在MySQL中，对索引的查看和删除操作是所有索引类型通用的，但创建方式则不一样。</p>
<h3 id="普通索引">普通索引</h3>
<p>这是最基本的索引，它没有任何限制，也是我们大多数情况下用到的索引。</p>
<p>1）创建普通索引</p>
<pre><code class="language-sql">CREATE INDEX index_name ON table_name (column(length));

ALTER TABLE table_name ADD INDEX index_name (column(length));

CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY
KEY(id) , INDEX index_name (title(5)));
</code></pre>
<p>2）查看索引</p>
<pre><code class="language-sql">SHOW INDEX FROM [table_name];

SHOW KEYS FROM [table_name] # 只在MySQL 中可以使用keys 关键字。
</code></pre>
<p>3）删除索引</p>
<pre><code class="language-sql">DROP INDEX index_name ON talbe_name;

ALTER TABLE table_name DROP INDEX index_name;

ALTER TABLE table_name DROP PRIMARY KEY;
</code></pre>
<h3 id="唯一索引">唯一索引</h3>
<p>与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。</p>
<p>如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似</p>
<p>创建索引：</p>
<pre><code class="language-sql">CREATE UNIQUE INDEX index_name ON table_name (column(length));

ALTER TABLE table_name ADD UNIQUE index_name (column(length));

CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY
KEY(id) , UNIQUE index_name (title(length)));
</code></pre>
<h3 id="全文索引fulltext">全文索引（FULLTEXT）</h3>
<p>MySQL 从3.23.23 版开始支持全文索引和全文检索，FULLTEXT 索引仅可用于MyISAM
表；他们可以从CHAR、VARCHAR 或TEXT 列中作为CREATE TABLE 语句的一部分被创建，或
是随后使用ALTER TABLE 或CREATE INDEX 被添加。</p>
<p>对于较大的数据集，将你的资料输入一个没有FULLTEXT 索引的表中，然后创建索引，
其速度比把资料输入现有FULLTEXT 索引的速度更为快。不过切记对于大容量的数据表，生
成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。</p>
<p>创建索引：</p>
<pre><code class="language-sql">CREATE FULLTEXT INDEX index_name ON table_name(column(length));

ALTER TABLE table_name ADD FULLTEXT index_name( column);

CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY
KEY(id) , FULLTEXT index_name (title));
</code></pre>
<h3 id="组合索引最左前缀">组合索引（最左前缀）</h3>
<pre><code class="language-sql">CREATE TABLE article(id int not null, title varchar(255), time date);
</code></pre>
<p>平时用的SQL 查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL 的效
率，就要考虑建立组合索引。例如上表中针对title 和time 建立一个组合索引：ALTER TABLE
article ADD INDEX index_title_time (title(50),time(10))。建立这样的组合索引，其实是相当于分
别建立了下面两组组合索引：</p>
<ul>
<li>title,time</li>
<li>title</li>
</ul>
<p>为什么没有time 这样的组合索引呢？这是因为MySQL 组合索引“最左前缀”的结果。</p>
<p>简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，</p>
<p>如下面的几个SQL 所示：</p>
<p>1，使用到上面的索引</p>
<pre><code class="language-sql">SELECT * FROM article WHERE title='测试' AND time=1234567890;
SELECT * FROM article WHERE title='测试';
</code></pre>
<p>2，不使用上面的索引</p>
<pre><code class="language-sql">SELECT * FROM article WHERE time=1234567890;
</code></pre>
<p>参考：https://segmentfault.com/a/1190000008131735#articleHeader5</p>
<p>创建索引：</p>
<pre><code class="language-sql">CREATE INDEX index_name ON table_name (column_list)
</code></pre>
<h2 id="mysql-中的索引优化">MySQL 中的索引优化</h2>
<p>虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE
和DELETE 次数大于查询次数时，放弃索引。</p>
<p>因为更新表时，MySQL 不仅要保存数据，还要
保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但
如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一
个因素，如果你的MySQL 有大数据量的表，就需要花时间研究建立最优秀的索引，或优化
查询语句。</p>
<h3 id="索引不会包含有null-值的列">索引不会包含有NULL 值的列</h3>
<p>只要列中包含有NULL 值都将不会被包含在索引中，组合索引中只要有一列含有NULL
值，那么这一列对于此组合索引就是无效的。所以我们在数据库设计时不要让字段的默认值
为NULL。</p>
<pre><code class="language-sql">create table table_name(c1 varchar(32) default ‘0’)
</code></pre>
<h3 id="使用短索引">使用短索引</h3>
<p>对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，
如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不
仅可以提高查询速度而且可以节省磁盘空间和I/O 操作。</p>
<pre><code class="language-sql">CREATE INDEX index_name ON table_name (column(length))
</code></pre>
<h3 id="索引列排序">索引列排序</h3>
<p>MySQL 查询只使用一个索引。因此如果where 子句中已经使用了索引的话，那么order
by 中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操
作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。</p>
<h3 id="like-语句操作">like 语句操作</h3>
<p>一般情况下不鼓励使用like 操作，如果非使用不可，如何使用也是一个问题。</p>
<p>like “%aaa%”不会使用索引，而like “aaa%”可以使用索引。</p>
<h3 id="不要在列上进行运算">不要在列上进行运算</h3>
<p>例如：<code>select * from users where YEAR(adddate) &lt; 2007</code>，将在每个行上进行运算，这将导
致索引失效而进行全表扫描， 因此我们可以改成： <code>select * from users where adddate &lt; '2007-01-01'</code></p>
<h2 id="索引总结">索引总结</h2>
<p>MySQL 只对以下操作符才使用索引：&lt;,&lt;=,=,&gt;,&gt;=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16 个索引，不过除
非是数据量真的很多，否则过多的使用索引也不是那么好玩的。</p>
<p>建议：一个表的索引数最好不要超过6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。</p>
<h1 id="mysql-中的sql-的常见优化策略">MySQL 中的SQL 的常见优化策略</h1>
<h2 id="避免全表扫描">避免全表扫描</h2>
<p>对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引。</p>
<h2 id="避免判断null-值">避免判断null 值</h2>
<p>应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：</p>
<pre><code class="language-sql">select id from t where num is null
</code></pre>
<p>可以在num 上设置默认值0，确保表中num 列没有null 值，然后这样查询：</p>
<pre><code class="language-sql">select id from t where num=0
</code></pre>
<h2 id="避免不等值判断">避免不等值判断</h2>
<p>应尽量避免在where 子句中使用!=或&lt;&gt;操作符，否则引擎将放弃使用索引而进行全表扫描。</p>
<h2 id="避免使用or-逻辑">避免使用or 逻辑</h2>
<p>应尽量避免在where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：</p>
<pre><code class="language-sql">select id from t where num=10 or num=20
</code></pre>
<p>可以这样查询：</p>
<pre><code class="language-sql">select id from t where num=10
union all
select id from t where num=20
</code></pre>
<h2 id="慎用in-和not-in-逻辑">慎用in 和not in 逻辑</h2>
<p>in 和not in 也要慎用，否则会导致全表扫描，如：</p>
<pre><code class="language-sql">select id from t1 where num in(select id from t2 where id &gt; 10)
</code></pre>
<p>此时外层查询会全表扫描，不使用索引。可以修改为：</p>
<pre><code class="language-sql">select id from t1,(select id from t1 where id &gt; 10)t2 where t1.id = t2.id
</code></pre>
<p>此时索引被使用，可以明显提升查询效率。</p>
<h2 id="注意模糊查询">注意模糊查询</h2>
<p>下面的查询也将导致全表扫描：</p>
<pre><code class="language-sql">select id from t where name like '%abc%'
</code></pre>
<p>模糊查询如果是必要条件时，可以使用</p>
<pre><code class="language-sql">select id from t where name like 'abc%'
</code></pre>
<p>来实现模糊查询，此时索引将被使用。如果头匹配是必要逻辑，建议使用全文搜索引擎（Elastic search、Lucene、Solr 等）。</p>
<h2 id="避免查询条件中字段计算">避免查询条件中字段计算</h2>
<p>应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：</p>
<pre><code class="language-sql">select id from t where num/2=100
</code></pre>
<p>应改为:</p>
<pre><code class="language-sql">select id from t where num=100*2
</code></pre>
<h2 id="避免查询条件中对字段进行函数操作">避免查询条件中对字段进行函数操作</h2>
<p>应尽量避免在where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：</p>
<pre><code class="language-sql">select id from t where substring(name,1,3)='abc'
</code></pre>
<p>&ndash;name以abc开头&lt;应改为:</p>
<pre><code class="language-sql">select id from t where name like 'abc%'
</code></pre>
<h2 id="where-子句左边注意点">WHERE 子句“=”左边注意点</h2>
<p>不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。</p>
<h2 id="组合索引使用">组合索引使用</h2>
<p>在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。</p>
<h2 id="不要定义无意义的查询">不要定义无意义的查询</h2>
<p>不要写一些没有意义的查询，如需要生成一个空表结构：</p>
<pre><code class="language-sql">select col1,col2 into #t from t where 1=0
</code></pre>
<p>这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：</p>
<pre><code class="language-sql">create table #t(...)
</code></pre>
<h2 id="exists">exists</h2>
<p>很多时候用exists 代替in 是一个好的选择：</p>
<pre><code class="language-sql">select num from a where num in(select num from b)
</code></pre>
<p>用下面的语句替换：</p>
<pre><code class="language-sql">select num from a where exists(select 1 from b where num=a.num)
</code></pre>
<h2 id="索引也可能失效">索引也可能失效</h2>
<p>并不是所有索引对查询都有效，SQL 是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL 查询可能不会去利用索引，如一表中有字段sex，male、female 几乎各一半，那么即使在sex 上建了索引也对查询效率起不了作用。</p>
<h2 id="表格字段类型选择">表格字段类型选择</h2>
<p>尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。</p>
<p>尽可能的使用varchar 代替char ，因为首先可变长度字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。</p>
<h2 id="查询语法中的字段">查询语法中的字段</h2>
<p>任何地方都不要使用select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。</p>
<h2 id="索引无关优化">索引无关优化</h2>
<p>不使用*、尽量不使用union，union all等关键字、尽量不使用or 关键字、尽量使用等值判断。</p>
<p>表连接建议不超过5 个。如果超过5 个，则考虑表格的设计。（互联网应用中）</p>
<p>表连接方式使用外联优于内联。</p>
<p>外连接有基础数据存在。如：A left join B，基础数据是A。A inner join B，没有基础数据的，先使用笛卡尔积完成全连接，在根据连接条件得到内连接结果集。</p>
<p>大数据量级的表格做分页查询时，如果页码数量过大，则使用子查询配合完成分页逻辑。</p>
<pre><code class="language-sql">Select * from table limit 1000000, 10
Select * from table where id in (select pk from table limit 100000, 10)
</code></pre>

    </div>
    <div class="article-footer">
<blockquote class="mt-2x">
  <ul class="post-copyright list-unstyled">
    <li class="post-copyright-link hidden-xs">
      <strong>Permalink: </strong>
      <a href="https://rulerLwx.gitee.io/2018/06/mysql-3-sql%E4%BC%98%E5%8C%96/" title="MySQL-3-SQL-优化" target="_blank" rel="external">https://rulerLwx.gitee.io/2018/06/mysql-3-sql%E4%BC%98%E5%8C%96/</a>
    </li>
    <li class="post-copyright-license">
      <strong>License：</strong><a href="http://creativecommons.org/licenses/by/4.0/deed.zh" target="_blank" rel="external">CC BY 4.0 CN</a>
    </li>
  </ul>
</blockquote>

<div class="panel panel-default panel-badger">
  <div class="panel-body">
    <figure class="media">
      <div class="media-left">
        <a href="https://gitee.com/rulerLwx" target="_blank" class="img-burn thumb-sm visible-lg">
          <img src="https://rulerLwx.gitee.io/avatar.png" class="img-rounded w-full" alt="">
        </a>
      </div>
      <div class="media-body">
        <h3 class="media-heading"><a href="https://gitee.com/rulerLwx" target="_blank"><span class="text-dark">rulerLwx</span><small class="ml-1x">thinking...</small></a></h3>
        <div>Good Good Study, Day Day Up~</div>
      </div>
    </figure>
  </div>
</div>
    </div>
  </article>
<section id="comments">
    <div id="vcomments"></div>
</section>

</div><nav class="bar bar-footer clearfix" data-stick-bottom>
    <div class="bar-inner">
        <ul class="pager pull-left">
            <li class="prev">
                <a href="https://rulerLwx.gitee.io/2018/06/java-front-javascript/" title="Java-front-JavaScript"><i
                        class="icon icon-angle-left"
                        aria-hidden="true"></i><span>&nbsp;&nbsp;Older</span></a>
            </li>
            <li class="next">
                <a href="https://rulerLwx.gitee.io/2018/06/mysql-2-%E5%9F%BA%E7%A1%80/"
                    title="MySQL-2-基础"><span>Newer&nbsp;&nbsp;</span><i
                        class="icon icon-angle-right" aria-hidden="true"></i></a>
            </li>
            
            <li class="toggle-toc">
                <a class="toggle-btn collapsed" data-toggle="collapse" href="#collapseToc" aria-expanded="false"
                    title="Catalogue" role="button">
                    <span>[&nbsp;</span><span>Catalogue</span>
                    <i class="text-collapsed icon icon-anchor"></i>
                    <i class="text-in icon icon-close"></i>
                    <span>]</span>
                </a>
            </li>
        </ul>
        <div class="bar-right">
            <div class="share-component" data-sites="weibo,qq,wechat"
                data-mobile-sites="weibo,qq,qzone"></div>
        </div>
    </div>
</nav>

</main><footer class="footer" itemscope itemtype="http://schema.org/WPFooter">
<ul class="social-links">
    <li><a href="https://gitee.com/rulerLwx" target="_blank" title="gitee" data-toggle=tooltip data-placement=top >
            <i class="icon icon-gitee"></i></a></li>
    <li><a href="https://github.com/wolf-lea" target="_blank" title="github" data-toggle=tooltip data-placement=top >
            <i class="icon icon-github"></i></a></li>
</ul>
  <div class="copyright">
    &copy;2017  -
    2020
    <div class="publishby">
        Theme by <a href="https://github.com/xiaoheiAh" target="_blank"> xiaoheiAh </a>base on<a href="https://github.com/xiaoheiAh/hugo-theme-pure" target="_blank"> pure</a>.
    </div>
  </div>
</footer>

<script src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.2/MathJax.js?config=TeX-MML-AM_SVG"></script>
<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
            showMathMenu: false, //disables context menu
            tex2jax: {
            inlineMath: [ ['$','$'], ['\\(','\\)'] ]
           }
    });
</script>


<script src="https://cdn.jsdelivr.net/npm/jquery@3.4.1/dist/jquery.min.js"></script>
<script>
    window.jQuery || document.write('<script src="js/jquery.min.js"><\/script>')
</script>
<script type="text/javascript" src="https://cdn.staticfile.org/highlight.js/9.15.10/highlight.min.js"></script>
<script type="text/javascript" src="https://cdn.staticfile.org/highlight.js/9.15.10/languages/python.min.js" defer></script>
<script type="text/javascript" src="https://cdn.staticfile.org/highlight.js/9.15.10/languages/javascript.min.js" defer></script><script>
    hljs.configure({
        tabReplace: '    ', 
        classPrefix: ''     
        
    })
    hljs.initHighlightingOnLoad();
</script>
<script src="https://rulerLwx.gitee.io/js/application.min.bdeb64b910570b6c41badc6a05b7afb0c8ad9efd8525de3c7257d59e786326a3.js"></script>
<script src="https://rulerLwx.gitee.io/js/plugin.min.51ff8c7317566f82259170fa36e09c4493adc9b9378b427a01ad3f017ebac7dd.js"></script>

<script>
    (function (window) {
        var INSIGHT_CONFIG = {
            TRANSLATION: {
                POSTS: 'Posts',
                PAGES: 'Pages',
                CATEGORIES: 'Categories',
                TAGS: 'Tags',
                UNTITLED: '(Untitled)',
            },
            ROOT_URL: 'https:\/\/rulerLwx.gitee.io',
            CONTENT_URL: 'https:\/\/rulerLwx.gitee.io\/searchindex.json ',
        };
        window.INSIGHT_CONFIG = INSIGHT_CONFIG;
    })(window);
</script>
<script type="text/javascript" src="https://rulerLwx.gitee.io/js/insight.min.a343cd9a5a7698336b28ef3a7c16a3a1b1d2d5fb17dc8ed04022bbe08cc5459073a15bdafa3a8a58cdd56080784bdd69fa70b1ae8597565c799c57ed00f0e120.js" defer></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/tocbot/4.4.2/tocbot.min.js"></script>
<script>
    tocbot.init({
        
        tocSelector: '.js-toc',
        
        contentSelector: '.js-toc-content',
        
        headingSelector: 'h1, h2, h3',
        
        hasInnerContainers: true,
    });
</script>

<script src="https://cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/valine"></script>
<script type="text/javascript">
    var GUEST = ['nick', 'mail', 'link'];
    var meta = 'nick,mail';
    meta = meta.split(',').filter(function (item) {
        return GUEST.indexOf(item) > -1;
    });
    new Valine({
        el: '#vcomments',
        verify: null ,
        notify: null ,
        appId: 'IyAB0PSPRazTPDxitO1ddQ7O-gzGzoHsz',
        appKey: '5rBJTq4KidYF33eXwvRVhtEH',
        placeholder: 'enjoy~',
        avatar: 'mm',
        meta: meta,
        pageSize: '10' || 10,
        visitor: false 
});
</script>

  </body>
</html>
